-- CRIA A TABELA NOVA
CREATE OR REPLACE FUNCTION func_createTable() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
    SELECT COUNT(pc.*) INTO VCOUNT FROM pg_class pc, pg_namespace pn WHERE pc.relname = 'tipo_carac' AND pc.relnamespace = pn.oid AND pn.nspname = '<SCHEMA>';

    IF VCOUNT = 0 THEN
        CREATE TABLE <SCHEMA>.TIPO_CARAC (
            COD_TIPO_CARAC NUMERIC(20, 0) NOT NULL,
            MNE_TIPO_CARAC VARCHAR(255) NOT NULL,
            CLASSE_CARAC_VALOR VARCHAR(512) NOT NULL);
    END IF;
    RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createTable();
 DROP FUNCTION func_createTable();


-- DROPA A PK
CREATE OR REPLACE FUNCTION func_dropToCreatePrimeryKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.tipo_carac'::REGCLASS AND conname= 'pk_tpcarc';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.TIPO_CARAC DROP CONSTRAINT PK_TPCARC CASCADE;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreatePrimeryKey();
 DROP FUNCTION func_dropToCreatePrimeryKey();

-- CRIA A PK
ALTER TABLE <SCHEMA>.TIPO_CARAC ADD CONSTRAINT PK_TPCARC PRIMARY KEY (COD_TIPO_CARAC);

-- DROPA A UNIQUE CONSTRAINT
CREATE OR REPLACE FUNCTION func_dropUniqueConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.tipo_carac'::REGCLASS AND conname= 'ak_tpcarc_0';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.tipo_carac DROP CONSTRAINT AK_TPCARC_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropUniqueConstraint();
 DROP FUNCTION func_dropUniqueConstraint();

-- CRIA A UNIQUE CONSTRAINT
ALTER TABLE <SCHEMA>.TIPO_CARAC ADD CONSTRAINT AK_TPCARC_0 UNIQUE (MNE_TIPO_CARAC);

-- CRIA A SEQUENCE
CREATE OR REPLACE FUNCTION func_createSequence() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_statio_all_sequences WHERE relname = 'seq_tpcarc' and schemaname = '<SCHEMA>';
      IF VCOUNT = 0 THEN
          CREATE SEQUENCE <SCHEMA>.SEQ_TPCARC START WITH 100000;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createSequence();
 DROP FUNCTION func_createSequence();


-- ADICIONA COMENTÁRIOS
COMMENT ON TABLE <SCHEMA>.TIPO_CARAC IS 'ARMAZENA OS TIPOS DE CARACTERISTICAS DO SISTEMA (STRING, DOUBLE, ETC). TABELA PREENCHIDA VIA POST SCRIPT. NAO HA CADASTRO NO SISTEMA PARA INCLUSAO/ALTERACAO DOS SEUS DADOS.';
COMMENT ON COLUMN <SCHEMA>.TIPO_CARAC.COD_TIPO_CARAC IS 'CODIGO DO TIPO DE CARACTERISTICA.';
COMMENT ON COLUMN <SCHEMA>.TIPO_CARAC.MNE_TIPO_CARAC IS 'MNEMONICO DO TIPO DE CARACTERISTICA.';
COMMENT ON COLUMN <SCHEMA>.TIPO_CARAC.CLASSE_CARAC_VALOR IS 'NOME DA CLASSE QUE REPRESENTA VALORES DESTE TIPO DE CARACTERISTICA.';

-- INSERÇÕES DO POSTSCRIPT
CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.TIPO_CARAC WHERE COD_TIPO_CARAC = 0;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.TIPO_CARAC SET MNE_TIPO_CARAC = 'DOUBLE', CLASSE_CARAC_VALOR = 'jmine.tec.aom.domain.impl.DoublePropertyValue' WHERE COD_TIPO_CARAC = 0;
  ELSE
    INSERT INTO <SCHEMA>.TIPO_CARAC (COD_TIPO_CARAC, MNE_TIPO_CARAC,                              CLASSE_CARAC_VALOR)
                             VALUES (             0,       'DOUBLE', 'jmine.tec.aom.domain.impl.DoublePropertyValue');
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();

CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.TIPO_CARAC WHERE COD_TIPO_CARAC = 1;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.TIPO_CARAC SET MNE_TIPO_CARAC = 'INTEGER', CLASSE_CARAC_VALOR = 'jmine.tec.aom.domain.impl.IntegerPropertyValue' WHERE COD_TIPO_CARAC = 1;
  ELSE
    INSERT INTO <SCHEMA>.TIPO_CARAC (COD_TIPO_CARAC, MNE_TIPO_CARAC,                              CLASSE_CARAC_VALOR)
                             VALUES (             1,       'INTEGER', 'jmine.tec.aom.domain.impl.IntegerPropertyValue');
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();

CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.TIPO_CARAC WHERE COD_TIPO_CARAC = 2;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.TIPO_CARAC SET MNE_TIPO_CARAC = 'BOOLEAN', CLASSE_CARAC_VALOR = 'jmine.tec.aom.domain.impl.BooleanPropertyValue' WHERE COD_TIPO_CARAC = 2;
  ELSE
    INSERT INTO <SCHEMA>.TIPO_CARAC (COD_TIPO_CARAC, MNE_TIPO_CARAC,                              CLASSE_CARAC_VALOR)
                             VALUES (             2,       'BOOLEAN', 'jmine.tec.aom.domain.impl.BooleanPropertyValue');
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();

CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.TIPO_CARAC WHERE COD_TIPO_CARAC = 3;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.TIPO_CARAC SET MNE_TIPO_CARAC = 'STRING', CLASSE_CARAC_VALOR = 'jmine.tec.aom.domain.impl.StringPropertyValue' WHERE COD_TIPO_CARAC = 3;
  ELSE
    INSERT INTO <SCHEMA>.TIPO_CARAC (COD_TIPO_CARAC, MNE_TIPO_CARAC,                              CLASSE_CARAC_VALOR)
                             VALUES (             3,       'STRING', 'jmine.tec.aom.domain.impl.StringPropertyValue');
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();

CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.TIPO_CARAC WHERE COD_TIPO_CARAC = 4;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.TIPO_CARAC SET MNE_TIPO_CARAC = 'DATE', CLASSE_CARAC_VALOR = 'jmine.tec.aom.domain.impl.DatePropertyValue' WHERE COD_TIPO_CARAC = 4;
  ELSE
    INSERT INTO <SCHEMA>.TIPO_CARAC (COD_TIPO_CARAC, MNE_TIPO_CARAC,                              CLASSE_CARAC_VALOR)
                             VALUES (             4,       'DATE', 'jmine.tec.aom.domain.impl.DatePropertyValue');
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();

CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.TIPO_CARAC WHERE COD_TIPO_CARAC = 5;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.TIPO_CARAC SET MNE_TIPO_CARAC = 'BIG_DECIMAL', CLASSE_CARAC_VALOR = 'jmine.tec.aom.domain.impl.BigDecimalPropertyValue' WHERE COD_TIPO_CARAC = 5;
  ELSE
    INSERT INTO <SCHEMA>.TIPO_CARAC (COD_TIPO_CARAC, MNE_TIPO_CARAC,                              CLASSE_CARAC_VALOR)
                             VALUES (             5,       'BIG_DECIMAL', 'jmine.tec.aom.domain.impl.BigDecimalPropertyValue');
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();


CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.TIPO_CARAC WHERE COD_TIPO_CARAC = 15;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.TIPO_CARAC SET MNE_TIPO_CARAC = 'ENUM', CLASSE_CARAC_VALOR = 'jmine.tec.aom.domain.impl.EnumPropertyValue' WHERE COD_TIPO_CARAC = 15;
  ELSE
    INSERT INTO <SCHEMA>.TIPO_CARAC (COD_TIPO_CARAC, MNE_TIPO_CARAC,                              CLASSE_CARAC_VALOR)
                             VALUES (             15,       'ENUM', 'jmine.tec.aom.domain.impl.EnumPropertyValue');
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();


CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.TIPO_CARAC WHERE COD_TIPO_CARAC = 17;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.TIPO_CARAC SET MNE_TIPO_CARAC = 'ENTITY', CLASSE_CARAC_VALOR = 'jmine.tec.aom.domain.impl.EntityPropertyValue' WHERE COD_TIPO_CARAC = 17;
  ELSE
    INSERT INTO <SCHEMA>.TIPO_CARAC (COD_TIPO_CARAC, MNE_TIPO_CARAC,                              CLASSE_CARAC_VALOR)
                             VALUES (             17,       'ENTITY', 'jmine.tec.aom.domain.impl.EntityPropertyValue');
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();


-- DROPA A CHECK CONSTRAINT
CREATE OR REPLACE FUNCTION func_dropCheckConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.caracteristicas'::REGCLASS AND conname= 'ck_caract_1';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.CARACTERISTICAS DROP CONSTRAINT CK_CARACT_1;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropCheckConstraint();
 DROP FUNCTION func_dropCheckConstraint();


-- ADICIONA A COLUNA NOVA
CREATE OR REPLACE FUNCTION func_add_column() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT count(*) into VCOUNT FROM pg_attribute WHERE attrelid='<SCHEMA>.caracteristicas'::REGCLASS AND attname = 'cod_tipo_carac';
  IF VCOUNT = 0 THEN
    ALTER TABLE <SCHEMA>.caracteristicas ADD cod_tipo_carac NUMERIC(20,0);
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT func_add_column();
DROP FUNCTION func_add_column();

-- ADICIONA COMENTÁRIO
COMMENT ON COLUMN <SCHEMA>.CARACTERISTICAS.COD_TIPO_CARAC IS 'CODIGO DO TIPO DE CARACTERISTICA.';

-- MIGRA OS DADOS E DROPA A COLUNA VELHA
CREATE OR REPLACE FUNCTION func_migrate_and_drop() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT count(*) into VCOUNT FROM pg_attribute WHERE attrelid='<SCHEMA>.caracteristicas'::REGCLASS AND attname = 'tp_dado_carac';
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.caracteristicas SET cod_tipo_carac = tp_dado_carac;
    ALTER TABLE <SCHEMA>.caracteristicas DROP tp_dado_carac;
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT func_migrate_and_drop();
DROP FUNCTION func_migrate_and_drop();

-- MARCA A COLUNA NOVA COMO NOT NULL APÓS A MIGRAÇÃO
ALTER TABLE <SCHEMA>.CARACTERISTICAS ALTER COLUMN COD_TIPO_CARAC SET NOT NULL;

-- DROPA A FK
CREATE OR REPLACE FUNCTION func_dropToCreateForeignKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.caracteristicas'::REGCLASS AND conname= 'fk_tpcarc_caract';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.CARACTERISTICAS DROP CONSTRAINT FK_TPCARC_CARACT;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreateForeignKey();
 DROP FUNCTION func_dropToCreateForeignKey();

-- CRIA A FK
ALTER TABLE <SCHEMA>.CARACTERISTICAS ADD CONSTRAINT FK_TPCARC_CARACT FOREIGN KEY (COD_TIPO_CARAC) REFERENCES <SCHEMA>.TIPO_CARAC (COD_TIPO_CARAC);


-- DROPA O INDEX DA FK
CREATE OR REPLACE FUNCTION func_dropIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_caract_jt_1' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 1 THEN
          DROP INDEX <SCHEMA>.IX_CARACT_JT_1;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropIndexElement();
 DROP FUNCTION func_dropIndexElement();


-- CRIA O INDEX DA FK
CREATE OR REPLACE FUNCTION func_createIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_caract_jt_1' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE INDEX IX_CARACT_JT_1 ON <SCHEMA>.CARACTERISTICAS (COD_TIPO_CARAC ASC);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createIndexElement();
 DROP FUNCTION func_createIndexElement();